package proyecto.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import javax.sql.DataSource;

import proyecto.exception.DAOExcepcion;
import proyecto.modelo.Pelicula;


public class PeliculaDAOImpl extends BaseDAO implements PeliculaDAO {

	private DataSource dataSource;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	public Collection<Pelicula> consulta_listado_filtros(String titPel, String genPel) throws DAOExcepcion {
		Collection<Pelicula> c = new ArrayList<Pelicula>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = dataSource.getConnection();
			String query = "select in_codPelicula, vc_tituloPelicula,vc_generoPelicula,vc_idiomaPelicula,bo_peliculaDoblada," +
					"bo_peliculaSubtitulada,bo_restriccionesPelicula,vc_censuraPelicula,bo_formato3D,da_fechaEstrenoPelicula," +
					"vc_imagenIOriginalPelicula,vc_imagen3DPelicula from pelicula where vc_tituloPelicula like ? and vc_generoPelicula like ?";
			stmt = con.prepareStatement(query);
			stmt.setString(1, "%" + titPel + "%");
			stmt.setString(2, "%" + genPel + "%");
			rs = stmt.executeQuery();
			while (rs.next()) {
				Pelicula vo = new Pelicula();
				vo.setIn_codPelicula(rs.getInt("in_codPelicula")); 
				vo.setVc_tituloPelicula(rs.getString("vc_tituloPelicula")); 
				vo.setVc_generoPelicula(rs.getString("vc_generoPelicula")); 
				vo.setVc_idiomaPelicula(rs.getString("vc_idiomaPelicula"));
				vo.setBo_peliculaDoblada(rs.getByte("bo_peliculaDoblada"));
				vo.setBo_peliculaSubtitulada(rs.getByte("bo_peliculaSubtitulada"));
				vo.setBo_restriccionesPelicula(rs.getByte("bo_restriccionesPelicula"));
				vo.setVc_censuraPelicula(rs.getString("vc_censuraPelicula"));
				vo.setBo_formato3D(rs.getByte("bo_formato3D"));
				vo.setDa_fechaEstrenoPelicula(rs.getDate("da_fechaEstrenoPelicula"));
				vo.setVc_imagenIOriginalPelicula("vc_imagenIOriginalPelicula");
				vo.setVc_imagen3DPelicula("vc_imagen3DPelicula");				
				c.add(vo);
				System.out.println("Registro: " + vo.getIn_codPelicula() + 
						 		   "," + vo.getVc_tituloPelicula() + 
						 		   "," + vo.getVc_generoPelicula() + 
						 		   "," + vo.getVc_idiomaPelicula());
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}
	
	public Pelicula detalle_pelicula(int in_codPelicula) throws DAOExcepcion {
		Pelicula vo = new Pelicula();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query =  "select in_codPelicula,vc_tituloPelicula,vc_tituloOriginal,vc_generoPelicula,vc_paisPelicula," +
							"vc_directorPelicula,vc_actoresPelicula,vc_idiomaPelicula,bo_peliculaDoblada," +
							"bo_peliculaSubtitulada,bo_restriccionesPelicula,vc_censuraPelicula,bo_formato3D,vc_sitioWebPelicula,da_fechaEstrenoPelicula," +
							"vc_imagenIOriginalPelicula,vc_imagen3DPelicula,vc_sinopsisPelicula,vc_enlaceTrailerPelicula,do_duracionPel " +
							"from pelicula where in_codPelicula=? ";
			con = dataSource.getConnection();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, in_codPelicula);
			rs = stmt.executeQuery();
			if (rs.next()) {
				vo.setIn_codPelicula(rs.getInt("in_codPelicula")); 
				vo.setVc_tituloPelicula(rs.getString("vc_tituloPelicula")); 
				vo.setVc_tituloOriginal(rs.getString("vc_tituloOriginal"));
				vo.setVc_generoPelicula(rs.getString("vc_generoPelicula")); 
				vo.setVc_generoPelicula(rs.getString("vc_generoPelicula"));
				vo.setVc_paisPelicula(rs.getString("vc_paisPelicula"));
				vo.setVc_directorPelicula(rs.getString("vc_directorPelicula"));
				vo.setVc_actoresPelicula(rs.getString("vc_actoresPelicula"));
				vo.setVc_idiomaPelicula(rs.getString("vc_idiomaPelicula"));
				vo.setBo_peliculaDoblada(rs.getByte("bo_peliculaDoblada"));
				vo.setBo_peliculaSubtitulada(rs.getByte("bo_peliculaSubtitulada"));
				vo.setBo_restriccionesPelicula(rs.getByte("bo_restriccionesPelicula"));
				vo.setVc_censuraPelicula(rs.getString("vc_censuraPelicula"));
				vo.setBo_formato3D(rs.getByte("bo_formato3D"));
				vo.setVc_sitioWebPelicula(rs.getString("vc_sitioWebPelicula"));
				vo.setDa_fechaEstrenoPelicula(rs.getDate("da_fechaEstrenoPelicula"));					
				vo.setVc_imagenIOriginalPelicula(rs.getString("vc_imagenIOriginalPelicula"));
				vo.setVc_imagen3DPelicula(rs.getString("vc_imagen3DPelicula"));
				vo.setVc_sinopsisPelicula(rs.getString("vc_sinopsisPelicula"));
				vo.setVc_enlaceTrailerPelicula(rs.getString("vc_enlaceTrailerPelicula"));
				vo.setDo_duracionPel(rs.getDouble("do_duracionPel"));
				System.out.println("Registro: " + vo.getIn_codPelicula() + 
				 		   "," + vo.getVc_tituloPelicula() + 
				 		   "," + vo.getVc_generoPelicula() + 
				 		   "," + vo.getVc_idiomaPelicula());				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}	
	
	public Pelicula insertar(Pelicula vo) throws DAOExcepcion {
		String query = "insert into pelicula (vc_tituloPelicula,vc_tituloOriginal,vc_generoPelicula,vc_paisPelicula,vc_directorPelicula,vc_actoresPelicula," +
				       "vc_idiomaPelicula,bo_peliculaDoblada,bo_peliculaSubtitulada,bo_restriccionesPelicula,vc_censuraPelicula,bo_formato3D," +
				       "vc_sitioWebPelicula,vc_imagenIOriginalPelicula,vc_imagen3DPelicula,vc_sinopsisPelicula," +
				       "vc_enlaceTrailerPelicula,do_duracionPel) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = dataSource.getConnection();
			stmt = con.prepareStatement(query);
			stmt.setString(1, vo.getVc_tituloPelicula());
			stmt.setString(2, vo.getVc_tituloOriginal());
			stmt.setString(3, vo.getVc_generoPelicula());
			stmt.setString(4, vo.getVc_paisPelicula());
			stmt.setString(5, vo.getVc_directorPelicula());
			stmt.setString(6, vo.getVc_actoresPelicula());
			stmt.setString(7, vo.getVc_idiomaPelicula());
			stmt.setByte(8, vo.getBo_peliculaDoblada());
			stmt.setByte(9, vo.getBo_peliculaSubtitulada());
			stmt.setByte(10, vo.getBo_restriccionesPelicula());
			stmt.setString(11, vo.getVc_censuraPelicula());
			stmt.setByte(12, vo.getBo_formato3D());
			stmt.setString(13, vo.getVc_sitioWebPelicula());
			//stmt.setString(14, vo.getDa_fechaEstrenoPelicula().toString());
			stmt.setString(14, vo.getVc_imagenIOriginalPelicula());
			stmt.setString(15, vo.getVc_imagen3DPelicula());
			stmt.setString(16, vo.getVc_sinopsisPelicula());
			stmt.setString(17, vo.getVc_enlaceTrailerPelicula());
			stmt.setDouble(18, vo.getDo_duracionPel());			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			} // Obtener el ultimo id 
			query = "SELECT LAST_INSERT_ID()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				vo.setIn_codPelicula(rs.getInt(1));
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}
	
	public Pelicula actualizar(Pelicula vo) throws DAOExcepcion {
		
		String query =  "update pelicula set vc_tituloPelicula=?,vc_tituloOriginal=?,vc_generoPelicula=?," +
						"vc_paisPelicula=?,vc_directorPelicula=?,vc_actoresPelicula=?,vc_idiomaPelicula=?," +
						"bo_peliculaDoblada=?,bo_peliculaSubtitulada=?,bo_restriccionesPelicula=?,vc_censuraPelicula=?," +
						"bo_formato3D=?,vc_sitioWebPelicula=?,vc_imagenIOriginalPelicula=?,vc_imagen3DPelicula=?,vc_sinopsisPelicula=?," +
						"vc_enlaceTrailerPelicula=?,do_duracionPel=? " +
						"where in_codPelicula=?";
		
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = dataSource.getConnection();
			stmt = con.prepareStatement(query);
			
			stmt.setString(1, vo.getVc_tituloPelicula());
			stmt.setString(2, vo.getVc_tituloOriginal());
			stmt.setString(3, vo.getVc_generoPelicula());
			stmt.setString(4, vo.getVc_paisPelicula());
			stmt.setString(5, vo.getVc_directorPelicula());
			stmt.setString(6, vo.getVc_actoresPelicula());
			stmt.setString(7, vo.getVc_idiomaPelicula());
			stmt.setByte(8, vo.getBo_peliculaDoblada());
			stmt.setByte(9, vo.getBo_peliculaSubtitulada());
			stmt.setByte(10, vo.getBo_restriccionesPelicula());
			stmt.setString(11, vo.getVc_censuraPelicula());
			stmt.setByte(12, vo.getBo_formato3D());
			stmt.setString(13, vo.getVc_sitioWebPelicula());
			//stmt.setString(14, vo.getDa_fechaEstrenoPelicula().toString());
			stmt.setString(14, vo.getVc_imagenIOriginalPelicula());
			stmt.setString(15, vo.getVc_imagen3DPelicula());
			stmt.setString(16, vo.getVc_sinopsisPelicula());
			stmt.setString(17, vo.getVc_enlaceTrailerPelicula());
			stmt.setDouble(18, vo.getDo_duracionPel());			
			stmt.setInt(19, vo.getIn_codPelicula());
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}	
	
}
